#!/bin/ksh

LOGIN=$1
SH_DIR=$2
UP_DIR=$3
UP_FILE=$4
BUSINESS=$5

sqlplus -s $LOGIN<<!
DELETE FROM ccs.ccs_ams_t_raw_data;
COMMIT;
!

cat > $UP_DIR/ctl/Upload_AMS.ctl<<!
LOAD DATA
INFILE '$UP_DIR/$UP_FILE'
APPEND INTO TABLE CCS.CCS_AMS_T_RAW_DATA
TRAILING NULLCOLS
(
ACCOUNT_NO					 POSITION(01:08)		CHAR						 "TRIM(:ACCOUNT_NO)",
ACCOUNT_STATUS 			 POSITION(09:09)		CHAR						 "TRIM(:ACCOUNT_STATUS)",
MOBILE_NO					 POSITION(10:17)		CHAR						 "TRIM(:MOBILE_NO)",
ACCOUNT_NAME				 POSITION(18:47)		CHAR						 "TRIM(:ACCOUNT_NAME)",
ID_NO 						 POSITION(48:57)		CHAR						 "TRIM(:ID_NO)",
BILL_CYCLE					 POSITION(58:59)		CHAR						 "TRIM(:BILL_CYCLE)",
CUSTOMER_CLASS 			 POSITION(60:64)		CHAR						 "TRIM(:CUSTOMER_CLASS)",
NETWORK_TYPE				 POSITION(65:65)		CHAR						 NULLIF NETWORK_TYPE=BLANKS,
NETWORK_SUBSCRIPTION 	 POSITION(66:66)		CHAR						 NULLIF NETWORK_SUBSCRIPTION=BLANKS,
SALES_PLAN					 POSITION(67:71)		CHAR						 "TRIM(:SALES_PLAN)",
AGENT_CODE					 POSITION(72:74)		CHAR						 "TRIM(:AGENT_CODE)",
ACTIVATION_DATE			 POSITION(75:82)		DATE(8)					 "yyyymmdd",
CONTRACT_CODE				 POSITION(83:85)		CHAR						 "TRIM(:CONTRACT_CODE)",
LAST_BILL_DATE 			 POSITION(86:93)		DATE(8)					 "yyyymmdd" NULLIF LAST_BILL_DATE=BLANKS,
LAST_PAYMENT_DATE 		 POSITION(94:101) 	DATE(8)					 "yyyymmdd" NULLIF LAST_PAYMENT_DATE=BLANKS,
LAST_PAYMENT				 POSITION(102:113)	DECIMAL EXTERNAL		 NULLIF LAST_PAYMENT=BLANKS,
IDD							 POSITION(114:114)	CHAR						 NULLIF IDD=BLANKS,
ROAM							 POSITION(115:115)	CHAR						 NULLIF ROAM=BLANKS,
DDA							 POSITION(116:116)	CHAR						 NULLIF DDA_LIMIT=BLANKS,
DDA_LIMIT					 POSITION(117:128)	DECIMAL EXTERNAL		 NULLIF DDA_LIMIT=BLANKS,
OVERDUE_BALANCE			 POSITION(129:140)	DECIMAL EXTERNAL		 NULLIF OVERDUE_BALANCE=BLANKS,
CURRENT_BALANCE			 POSITION(141:152)	DECIMAL EXTERNAL		 NULLIF CURRENT_BALANCE=BLANKS,
UNBILL_BALANCE 			 POSITION(153:164)	DECIMAL EXTERNAL		 NULLIF UNBILL_BALANCE=BLANKS,
DEPOSIT_BALANCE			 POSITION(165:176)	DECIMAL EXTERNAL		 NULLIF DEPOSIT_BALANCE=BLANKS,
TOTAL_BALANCE				 POSITION(177:188)	DECIMAL EXTERNAL		 NULLIF TOTAL_BALANCE=BLANKS,
UB_LOCAL 					 POSITION(189:200)	DECIMAL EXTERNAL		 NULLIF UB_LOCAL=BLANKS,
UB_IDDR						 POSITION(201:212)	DECIMAL EXTERNAL		 NULLIF UB_IDDR=BLANKS,
BLACKLIST_IND				 POSITION(213:213)	CHAR						 NULLIF BLACKLIST_IND=BLANKS,
OPERATOR_HKT				 POSITION(214:214)	CHAR						 NULLIF OPERATOR_HKT=BLANKS,
OPERATOR_SMC				 POSITION(215:215)	CHAR						 NULLIF OPERATOR_SMC=BLANKS,
OPERATOR_HTL				 POSITION(216:216)	CHAR						 NULLIF OPERATOR_HTL=BLANKS,
ASSOCIATE_ACCOUNT 		 POSITION(217:217)	CHAR						 NULLIF ASSOCIATE_ACCOUNT=BLANKS,
CONTACT_HOME_NO			 POSITION(218:225)	CHAR						 NULLIF CONTACT_HOME_NO=BLANKS,
CONTACT_OFFICE_NO 		 POSITION(226:233)	CHAR						 NULLIF CONTACT_OFFICE_NO=BLANKS,
CONTACT_MOBILE_NO 		 POSITION(234:241)	CHAR						 NULLIF CONTACT_MOBILE_NO=BLANKS,
ADDRESS_LINE_3 			 POSITION(242:281)	CHAR						 NULLIF ADDRESS_LINE_3=BLANKS "TRIM(:ADDRESS_LINE_3)",
ADDRESS_LINE_4 			 POSITION(282:321)	CHAR						 NULLIF ADDRESS_LINE_4=BLANKS "TRIM(:ADDRESS_LINE_4)",
ADDRESS_LINE_5 			 POSITION(322:361)	CHAR						 NULLIF ADDRESS_LINE_5=BLANKS "TRIM(:ADDRESS_LINE_5)",
ADDRESS_LINE_CITY 		 POSITION(362:381)	CHAR						 NULLIF ADDRESS_LINE_CITY=BLANKS "TRIM(:ADDRESS_LINE_CITY)",
BIRTHDAY_DATE				 POSITION(382:389)	DATE(8)					 "yyyymmdd" NULLIF BIRTHDAY_DATE=BLANKS,
GENDER						 POSITION(390:390)	CHAR						 NULLIF GENDER=BLANKS,
ADDRESS_PROOF				 POSITION(391:392)	CHAR						 NULLIF ADDRESS_PROOF=BLANKS "TRIM(:ADDRESS_PROOF)",
SEQ_NO																				 SEQUENCE(MAX,1),
BUSINESS_TYPE				 CONSTANT											'$BUSINESS',
UPLOAD_FILE 				 CONSTANT											'$UP_FILE'
)
!

/usr/local/bin/dos2unix $UP_DIR/$UP_FILE
sqlldr $LOGIN \
control=$UP_DIR/ctl/Upload_AMS.ctl bad=$UP_DIR/bad/Upload_AMS.bad \
log=$UP_DIR/log/Upload_AMS.log >> $UP_DIR/log/Upload_AMS.log
